#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
基于英文变量名的S7协议变量配置数据导入脚本
功能：读取Excel文件中的PLC变量数据，使用英文变量名作为主键，导入到s7_variables_config表中
作者：系统生成
日期：2025-08-30
"""

import pandas as pd
import pymysql
import re
import sys
from datetime import datetime
import logging

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('s7_import_en_name.log', encoding='utf-8'),
        logging.StreamHandler(sys.stdout)
    ]
)
logger = logging.getLogger(__name__)

class S7DataImporterEnName:
    def __init__(self):
        # 数据库连接配置
        self._config = {
            'host': 'ldkjmysql.rwlb.rds.aliyuncs.com',
            'user': 'jiarelu',
            'password': 'jiareluLDKJ123!',
            'database': 'jrl_lab',
            'charset': 'utf8mb4'
        }
        
        # S7默认配置
        self.s7_defaults = {
            'plc_ip': '192.168.0.1',
            'plc_port': 102,
            'rack': 0,
            'slot': 2
        }
        
        self.connection = None
    
    def connect_database(self):
        """连接数据库"""
        try:
            self.connection = pymysql.connect(**self.db_config)
            logger.info("数据库连接成功")
            return True
        except Exception as e:
            logger.error(f"数据库连接失败: {e}")
            return False
    
    def close_connection(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            logger.info("数据库连接已关闭")
    
    def parse_address(self, address_str):
        """
        解析地址字符串，提取DB号和起始地址
        例：DB24,DD0 -> db_number=24, start_address=DD0
        """
        if not address_str or pd.isna(address_str):
            return None, None
        
        address_str = str(address_str).strip()
        
        # 匹配DB号和地址的正则表达式
        pattern = r'DB(\d+),(.+)'
        match = re.match(pattern, address_str)
        
        if match:
            db_number = int(match.group(1))
            start_address = match.group(2).strip()
            return db_number, start_address
        else:
            logger.warning(f"无法解析地址格式: {address_str}")
            return None, None
    
    def read_excel_data(self, excel_file):
        """读取Excel文件数据"""
        try:
            # 读取Excel文件
            df = pd.read_excel(excel_file, engine='openpyxl')
            logger.info(f"成功读取Excel文件，共{len(df)}行数据")
            
            # 显示列名，便于调试
            logger.info(f"Excel列名: {list(df.columns)}")
            
            return df
        except Exception as e:
            logger.error(f"读取Excel文件失败: {e}")
            return None
    
    def process_data(self, df):
        """处理数据，转换为数据库格式"""
        processed_data = []
        
        for index, row in df.iterrows():
            try:
                # 跳过空行或无效行
                if pd.isna(row.get('名称')) or pd.isna(row.get('地址')):
                    continue
                
                # 检查英文变量名是否存在
                en_name = row.get('参数英文名称')
                if pd.isna(en_name) or not str(en_name).strip():
                    logger.warning(f"第{index+2}行缺少英文变量名，跳过")
                    continue
                
                # 解析地址
                db_number, start_address = self.parse_address(row.get('地址'))
                if db_number is None or start_address is None:
                    logger.warning(f"第{index+2}行地址解析失败，跳过")
                    continue
                
                # 处理是否激活字段
                is_active = '1'  # 默认激活
                if '是否激活' in df.columns:
                    active_value = row.get('是否激活')
                    if pd.notna(active_value):
                        is_active = '1' if str(active_value).strip() == '1' else '0'
                
                # 构建数据记录
                record = {
                    'variable_name': str(row.get('名称', '')).strip(),
                    'variable_en_name': str(en_name).strip(),
                    'plc_ip': self.s7_defaults['plc_ip'],
                    'plc_port': self.s7_defaults['plc_port'],
                    'rack': self.s7_defaults['rack'],
                    'slot': self.s7_defaults['slot'],
                    'data_type': str(row.get('数据类型', 'Real')).strip(),
                    'db_number': db_number,
                    'start_address': start_address,
                    'is_active': is_active,
                    'create_by': 'system',
                    'create_time': datetime.now(),
                    'update_by': 'system',
                    'update_time': datetime.now()
                }
                
                processed_data.append(record)
                logger.debug(f"处理第{index+2}行: {record['variable_name']} -> {record['variable_en_name']}")
                
            except Exception as e:
                logger.error(f"处理第{index+2}行数据时出错: {e}")
                continue
        
        logger.info(f"成功处理{len(processed_data)}条数据")
        return processed_data
    
    def check_duplicates_by_en_name(self, data):
        """基于英文变量名检查重复数据"""
        if not self.connection:
            return data
        
        try:
            cursor = self.connection.cursor()
            unique_data = []
            
            for record in data:
                # 基于英文变量名检查重复
                if record.get('variable_en_name'):
                    check_sql = """
                    SELECT COUNT(*) FROM s7_variables_config 
                    WHERE plc_ip = %s AND variable_en_name = %s
                    """
                    cursor.execute(check_sql, (record['plc_ip'], record['variable_en_name']))
                    count = cursor.fetchone()[0]
                    
                    if count == 0:
                        unique_data.append(record)
                    else:
                        logger.warning(f"跳过重复数据(基于英文名称): {record['plc_ip']} - {record['variable_en_name']}")
                else:
                    logger.warning(f"跳过没有英文名称的数据: {record['variable_name']}")
            
            cursor.close()
            logger.info(f"去重后剩余{len(unique_data)}条数据")
            return unique_data
            
        except Exception as e:
            logger.error(f"检查重复数据时出错: {e}")
            return data
    
    def insert_data(self, data):
        """插入数据到数据库"""
        if not self.connection or not data:
            return False
        
        try:
            cursor = self.connection.cursor()
            
            # 插入SQL语句
            insert_sql = """
            INSERT INTO s7_variables_config (
                variable_name, variable_en_name, plc_ip, plc_port, rack, slot,
                data_type, db_number, start_address, is_active,
                create_by, create_time, update_by, update_time
            ) VALUES (
                %(variable_name)s, %(variable_en_name)s, %(plc_ip)s, %(plc_port)s,
                %(rack)s, %(slot)s, %(data_type)s, %(db_number)s, %(start_address)s,
                %(is_active)s, %(create_by)s, %(create_time)s, %(update_by)s, %(update_time)s
            )
            """
            
            # 批量插入
            success_count = 0
            for record in data:
                try:
                    cursor.execute(insert_sql, record)
                    success_count += 1
                except Exception as e:
                    logger.error(f"插入数据失败: {record['variable_en_name']} - {e}")
                    continue
            
            # 提交事务
            self.connection.commit()
            cursor.close()
            
            logger.info(f"成功插入{success_count}条数据")
            return True
            
        except Exception as e:
            logger.error(f"插入数据时出错: {e}")
            if self.connection:
                self.connection.rollback()
            return False
    
    def clear_existing_data(self):
        """清空现有数据"""
        try:
            cursor = self.connection.cursor()
            
            # 查询现有数据条数
            count_sql = "SELECT COUNT(*) FROM s7_variables_config"
            cursor.execute(count_sql)
            current_count = cursor.fetchone()[0]
            
            if current_count > 0:
                logger.info(f"清空现有的 {current_count} 条数据")
                clear_sql = "DELETE FROM s7_variables_config"
                cursor.execute(clear_sql)
                
                # 重置自增ID
                reset_sql = "ALTER TABLE s7_variables_config AUTO_INCREMENT = 1"
                cursor.execute(reset_sql)
                
                self.connection.commit()
                logger.info("现有数据已清空")
            
            cursor.close()
            
        except Exception as e:
            logger.error(f"清空现有数据时出错: {e}")
    
    def import_excel_to_database(self, excel_file, clear_existing=False):
        """主要导入流程"""
        logger.info("开始S7变量配置数据导入(基于英文变量名)...")
        
        # 1. 连接数据库
        if not self.connect_database():
            return False
        
        try:
            # 2. 是否清空现有数据
            if clear_existing:
                self.clear_existing_data()
            
            # 3. 读取Excel数据
            df = self.read_excel_data(excel_file)
            if df is None:
                return False
            
            # 4. 处理数据
            processed_data = self.process_data(df)
            if not processed_data:
                logger.warning("没有有效数据需要导入")
                return False
            
            # 5. 检查重复数据
            unique_data = self.check_duplicates_by_en_name(processed_data)
            
            # 6. 插入数据
            success = self.insert_data(unique_data)
            
            if success:
                logger.info("数据导入完成!")
                return True
            else:
                logger.error("数据导入失败!")
                return False
                
        finally:
            # 7. 关闭数据库连接
            self.close_connection()

def main():
    """主函数"""
    excel_file = "加热炉实验室plc数据信息_处理后.xlsx"
    
    # 检查文件是否存在
    import os
    if not os.path.exists(excel_file):
        logger.error(f"Excel文件不存在: {excel_file}")
        return
    
    # 创建导入器并执行导入
    importer = S7DataImporterEnName()
    
    # 询问是否清空现有数据
    clear_existing = input("是否清空现有数据重新导入？(y/N): ")
    clear_flag = clear_existing.lower() in ['y', 'yes']
    
    success = importer.import_excel_to_database(excel_file, clear_existing=clear_flag)
    
    if success:
        print("✅ 数据导入成功!")
    else:
        print("❌ 数据导入失败，请检查日志文件")

if __name__ == "__main__":
    main()